package DB;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;


import BO.Item;

public class ItemDB extends Item {

	protected ItemDB(int itemId, int category, String name, float price, String description, int amount) {
		super(itemId, category, name, price, description, amount);
	}
	
	/**
	 * Get information about an item.
	 * @param id - The item id that you are interested in.
	 * @return - Item containing information.
	 */
	public static Item getItemById(int id){
		Connection connection = ConnectionPool.getConnection();
		Item item = null;
		try {
			Statement stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery("select * from T_ITEM where item_id = " + id + ";");
			item = getItemFromResultSet(rs);
			rs.close();
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
		ConnectionPool.releaseConnection(connection);
		return item;
	}
	
	/**
	 * Get all the items that in a specified category.
	 * @param categoryName - The category name.
	 * @return - All items in that category.
	 */
	public static ArrayList<Item> getItemsByCategoryName(String categoryName){
		Connection connection = ConnectionPool.getConnection();
		ArrayList<Item> items = new ArrayList<Item>();
		try {
			Statement stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery("select * from T_ITEM i, T_CATEGORY c where c.category_id = i.category and c.name = \"" + categoryName + "\";");
			items = getItemsFromResultSet(rs);
			rs.close();
			stmt.close();
		} catch (SQLException e){}
		return items;
	}
	
	/**
	 * Create a new item.
	 * @param category - The category the item should be found under.
	 * @param name - The name of the item.
	 * @param price - The price of the item.
	 * @param description - A description about the item.
	 * @param amount - The amount you got in stock.
	 */
	public static void createItem(int category, String name, float price, String description, int amount){
		Connection connection = ConnectionPool.getConnection();
		try {
			Statement stmt = connection.createStatement();
			stmt.executeUpdate("insert into T_ITEM (category,name,price,description,amount) values (" +
					category + ",\"" + name + "\"," + price + ",\"" + description + "\"," + amount + ");");
			stmt.close();
		} catch (SQLException e){
			throw new RuntimeException(e);
		}
		ConnectionPool.releaseConnection(connection);
	}
	
	/**
	 * Update a specific item.
	 * @param id - Id of the item to be updated.
	 * @param category - The new Category the item should be under.
	 * @param name - The new name of the item.
	 * @param price - The new price of the item.
	 * @param description - A new description about the item.
	 * @param amount - The new amount you got in stock.
	 */
	public static void updateItem(int id, int category, String name, float price, String description, int amount){
		Connection connection = ConnectionPool.getConnection();
		try {
			Statement stmt = connection.createStatement();
			stmt.executeUpdate("update T_ITEM set category = " + category + ", name = \"" + name + "\", price = " + price + ", description =\"" + description + "\", amount = " + amount + " where item_id = " + id + ";");
			stmt.close();
		} catch (SQLException e){
			e.printStackTrace();
		}
		ConnectionPool.releaseConnection(connection);
	}
	
	/*
	 * Help method to get info about a item from the ResultSet. 
	 * @param rs - ResultSet to look for info.
	 * @return - The item containing the info.
	 */
	private static Item getItemFromResultSet(ResultSet rs){
		try {
			if(rs.next()){
				int itemId = rs.getInt("item_id");
				int category = rs.getInt("category");
				String name = rs.getString("name");
				float price = rs.getFloat("price");
				String description = rs.getString("description");
				int amount = rs.getInt("amount");
				return new ItemDB(itemId,category,name,price,description,amount);				
			}
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
		return null;
	}
	
	/*
	 * Help method to get info about many items from the ResultSet. 
	 * @param rs - ResultSet to look for info.
	 * @return - All the items.
	 */
	private static ArrayList<Item> getItemsFromResultSet(ResultSet rs){
		ArrayList<Item> items = new ArrayList<Item>();
		try {
			while(rs.next()){
				int itemId = rs.getInt("item_id");
				int category = rs.getInt("category");
				String name = rs.getString("name");
				float price = rs.getFloat("price");
				String description = rs.getString("description");
				int amount = rs.getInt("amount");
				items.add(new ItemDB(itemId,category,name,price,description,amount));
			}
		} catch (SQLException e){
			
		}
		return items;
	}

}
